엑셀 OFFSET 함수는 시작 지점에서 일정 거리만큼 떨어진 셀이나 범위를 참조하는 함수입니다.
=OFFSET(시작지점, 행이동, 열이동, [높이], [너비])
- 단일 셀 또는 범위를 지정합니다. 만약 범위를 지정할 경우, 왼쪽 상단 첫번째 셀이 기준점이 됩니다.
- 양수는 아래로, 음수는 위로 이동합니다. 0을 입력하면 이동하지 않습니다.
- 양수는 오른쪽으로, 음수는 왼쪽으로 이동합니다. 0을 입력하면 이동하지 않습니다.
- 0을 입력하면 #REF 오류가 반환됩니다.
- 양수는 아래, 음수는 위로 확장된 범위를 반환합니다.
- 음수를 입력하면 왼쪽으로 확장된 범위를 반환합니다.
엑셀 OFFSET 함수는 시작 지점으로부터 지정한 행과 열 거리를 이동한 후, 셀 또는 범위를 반환하는 함수입니다. 단순히 셀을 반환하는 것뿐만 아니라, 데이터가 추가되거나 바뀌었을 때 범위를 동적으로 참조하는 동적 범위를 만들 때 사용할 수 있습니다.
=OFFSET(A1,5,5)/ / A1셀에서 오른쪽으로 5칸, 아래로 5칸을 이동한 F6셀을 참조합니다. =OFFSET(A1,0,0,3,3)/ / A1셀에서 가로로 3칸, 아래로 3칸 확장한 A1:C3 범위를 참조합니다.
OFFSET 함수는 주로 INDEX 함수와 INDIRECT 함수 등 다른 함수와 조합하여 사용되며, 특히 복잡한 범위를 참조해야 하는 데이터 분석이나 보고서를 작성할 때 효과적입니다. 예를 들어, 매일 누적되는 데이터를 관리해야 할 때 동적 범위를 활용하면 다양한 보고서를 손쉽게 자동화 할 수 있습니다. 이 동적범위에 대한 자세한 설명은 아래 영상강의를 참고하세요!👇
=OFFSET(A1,3) / / A1셀에서 3칸 아래로 이동한 A4셀을 참조합니다.
A열에 일별 매출 데이터가 입력되어 있을 경우, 다음과 같이 수식을 작성하면 최근 3개 항목의 합계를 구할 수 있습니다.
=SUM(OFFSET(A1, COUNTA(A:A)-3, 0, 3, 1)) / / OFFSET 함수는 A1셀에서 "A열 데이터 개수-3" 만큼 아래로 이동한 후, 3칸 확장된 범위를 반환합니다. / / 이후 SUM 함수로 해당 범위의 합계를 구합니다.
만약 1월부터 12월까지 시트가 구분되어 있고, 각 시트의 A1:A10 범위의 합계를 동적으로 구하는 상황을 가정하겠습니다. B1셀에 시트 이름이 작성된 경우, 다음과 같이 수식을 작성합니다.
=SUM(OFFSET(INDIRECT("'"&B1&"'!A1"),0,0,10,1)) / / B1셀에 입력된 시트의 A1:A10 범위의 합계를 구합니다.
엑셀 2019 이전 버전에서는 수식의 배열 결과를 동적으로 출력하는 분산 범위 기능이 제공되지 않습니다. 따라서, 높이와 너비를 지정한 경우 범위를 넓게 선택한 상태에서 함수를 입력하고 Ctrl + Shift + Enter로 수식을 입력합니다.
=OFFSET(A1,0,0,5,5) / / 먼저 범위를 넓게 선택한 후, F2키를 누르거나 수식입력줄을 선택하고 OFFSET 함수를 Ctrl + Shift + Enter로 입력합니다. / / 함수로 반환되는 크기보다 넓은 범위를 선택한 경우, 나머지 비어있는 범위에는 #N/A 가 출력됩니다.
만약 OFFSET 함수의 결과 범위가 시트를 벗어날 경우 #REF! 오류를 반환합니다.
예) =OFFSET(A1,-1,-1) → A1셀의 위쪽으로 범위가 벗어나 #REF! 오류를 반환합니다.
높이나 너비를 생략할 경우, OFFSET 함수는 기본값으로 셀 1칸을 참조합니다.
OFFSET 함수는 셀을 실제로 이동하거나 변경하지 않고, 기준 셀에서 이동한 범위만 반환합니다.
offset(시작지점,행이동,열이동,[높이],[너비])가 평균점수에서 어떻게 나오는건지 잘 모르겠습니다
부연설명 부탁드려도 될까요?
높이와 너비를 지정하면 OFFSET 함수 결과로 범위가 반환되는데요.
반환된 범위를 대상으로 AVERAGE 함수를 사용하면, 범위의 평균이 계산됩니다.
오빠두 님의 자료에 제 나름대로 간단히 설명을 달아봤습니다.
OFFSET 구문이 아래와 같은데
=OFFSET (시작지점, 행이동, 열이동, [높이], [너비])
예제에서는 =OFFSET(B7,H7,1,1,3))
참조가 B7, H7 이렇게 두개가 걸려 있어서
어느 부분이 시작지점인지, 그리고 셀 두개가 참조될 때
는 그 내용이 무엇인지 궁금합니다 ㅠ
=OFFSET(B7,H7,1,1,3))
함수에서 시작셀은 B7셀입니다.^^
H7은 아래로 이동할 값을 나타냅니다. 예를 들어, H7셀에 2가 입력되어 있으면, B7셀에서 아래로 2칸 이동합니다.
죄송하지만 OFFSET을 사용할때 시작지점(reference)에 vlookup함수나 match함수를 써서 다른 A시트의 특정셀을 지정하여 A시트의 참조범위를 만들고 싶은데 계속 오류가 나네요. 동일 함수로 스프레드시트에서는 가능한데, 엑셀365에서는 안되던데. 이유가 있을까요?
시작셀 범위로 INDIRECT 함수를 사용해 지정하면 바로 해결되실 듯 합니다.^^ 한번 테스트해보세요.
감사합니다.
=OFFSET(손익증감!$B$2,ROW($A1)-1,MATCH($P$2,손익증감!$1:$1,0)-2)
위 함수가 어떻게 적용되는지 자세한 원리를 알고싶습니다~
특히, -1 과 -2 가 무슨이유로 들어간건지 궁금합니다~
자세한 설명 부탁드립니다~~
감사합니다~~^^
ROW($A1) 선택 후 F9 키를 누르면 계산값을 확인할 수 있습니다. 직접 F9키를 통해 수식 동작을 검토해보세요.
오빠두엑셀님께 메일보내기도 안되고 아주 미치겠네요~~
어떻게 해결방법이 없을까요?
캡쳐사진을 첨가하려고 해도 안됩니다~~
제발 부탁드립니다~ 아주 미치겠네요....
업무 공부하는데 많은 도움이 되고 있습니다.
다름이 아니라 하기와 같은 일정간격이 떨어진 숫자의 합을 구하는 방법이 궁금합니다. 공유주신 OFFSET함수로 일단 작업해보았습니다만, 이것도 약간 노가다 느낌이 나서요,
아래 수식을 사용해보시길 바랍니다.
2021 이전 버전에서는 Ctrl + Shift + Enter로 입력하시면 됩니다.
감사합니다.
다만, 제가 필요한 것은 하기와 같이 월별 금액이 있고, 홀수월의 합을 구해야하는 로직입니다.
공유주신 함수로 확인하면 COLUME함수가 B열의 값인 2로 반영되어, MOD함수의 나머지값이 0으로 그냥 전체범위의 합계를 계산하는 식이 되어버리는데, 제가 혹시 범위를 잘못 설정했을까요?
버전에 따라 참조방식으로 인한 문제가 있을 수 있습니다.
범위를 절대참조로 바꾼 후, Ctrl + Shift + Enter로 입력해보시길 바랍니다. 수식에는 이상이 없습니다. 아래 그림을 참고하세요.
감사합니다.
=OFFSET(A1,3,,1,3)
'A1셀에서 아래로 3칸 이동한 뒤(=A4),아래로 1칸, 오른쪽으로 3칸 확장한 범위(A4:C5)' 라고 되어있는데요.
A4에서 높이X1 너비X3 해서 범위값은 (A4:C4)가 나와야 하는 것 아닌가요?
네 말씀하신 내용이 맞습니다. A4:C4가 반환되어야 합니다. :)
해당 내용은 방금 수정하였습니다. 잘못된 부분을 확인해주셔서 감사드립니다.